﻿using System;
using System.Collections.Generic;
using System.Web.Configuration;
using System.Web;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data;

namespace BT_web__nhom06.Tienichsql
{
    public class ketnoicsdl
    {
        public static string laychuoiketnoi()
        {
            return WebConfigurationManager.ConnectionStrings["dbconnect"].ConnectionString;
        }

        public static int chaylenhcapnhatdulieu(string lenhsql)
        {
            using (SqlConnection cnn = new SqlConnection())
            {
                cnn.ConnectionString = laychuoiketnoi();
                cnn.Open();
                SqlCommand cm = cnn.CreateCommand();
                cm.CommandText = lenhsql;
                return cm.ExecuteNonQuery();
            }
        }
        public static int chaylenhcapnhatdulieu(string lenhsql, string[] arrtenthamso, object[] arrgiatrithamso)
        {
         using (SqlConnection cnn = new SqlConnection())
            {
                cnn.ConnectionString = laychuoiketnoi();
                cnn.Open();
                SqlCommand cm = cnn.CreateCommand();
                cm.CommandText = lenhsql;
             //gan tham so cho cau lenh 
                for(int i = 0; i< arrtenthamso.Length;i++)
                {
                    cm.Parameters.AddWithValue(arrtenthamso[i], arrgiatrithamso[i]);
                }
                return cm.ExecuteNonQuery();
            }
        }
        public static int ChayLenhUpdateDuLieu(string lenhSql,
                                               params object[] thamSo)
        {
            using (SqlConnection cnn = new SqlConnection())
            {
                cnn.ConnectionString = laychuoiketnoi();
                cnn.Open(); //Mo ket noi
                SqlCommand cmd = cnn.CreateCommand(); //Tao doi tuong Command
                cmd.CommandText = lenhSql;
                //Truyen tham so cho command
                for (int i = 0; i < thamSo.Length - 1; i += 2)
                {
                    cmd.Parameters.AddWithValue(thamSo[i].ToString(), thamSo[i + 1]);
                }
                return cmd.ExecuteNonQuery();
            }
        }
        public static DataTable ChayLenhSelectDuLieu(string lenhSqlSelect,
                                                  string[] arrTenThamSo,
                                                object[] arrGiaTriThamSo)
        {
            using (SqlConnection cnn = new SqlConnection())
            {
                cnn.ConnectionString = laychuoiketnoi();
                cnn.Open(); //Mo ket noi
                SqlCommand cmd = cnn.CreateCommand(); //Tao doi tuong Command
                cmd.CommandText = lenhSqlSelect;
                //Truyen tham so cho command
                for (int i = 0; i < arrTenThamSo.Length; i++)
                {
                    cmd.Parameters.AddWithValue(arrTenThamSo[i], arrGiaTriThamSo[i]);
                }
                SqlDataReader reader = cmd.ExecuteReader();
                DataTable tblKetQua = new DataTable();
                tblKetQua.Load(reader);
                return tblKetQua;
            }
        }

        /// <summary>
        /// Chay cau lenh select
        /// </summary>
        /// <param name="lenhSqlSelect"></param>
        /// <param name="thamSo">"@TenThamSo", giaTri</param>
        /// <returns></returns>
        //Vi du:  ChayLenhSelectDuLieu( sqlSelect, "@Username",txtUsername.Text, "@Passowrd", txtPassword.Text );
        public static DataTable ChayLenhSelectDuLieu(string lenhSqlSelect,
                                                    params object[] thamSo)
        {
            using (SqlConnection cnn = new SqlConnection())
            {
                cnn.ConnectionString = laychuoiketnoi();
                cnn.Open(); //Mo ket noi
                SqlCommand cmd = cnn.CreateCommand(); //Tao doi tuong Command
                cmd.CommandText = lenhSqlSelect;
                //Truyen tham so cho command
                for (int i = 0; i < thamSo.Length - 1; i += 2)
                {
                    cmd.Parameters.AddWithValue(thamSo[i].ToString(), thamSo[i + 1]);
                }
                SqlDataReader reader = cmd.ExecuteReader();
                DataTable tblKetQua = new DataTable();
                tblKetQua.Load(reader);
                return tblKetQua;
            }
        }

        //validation control (các điều khiển trong dữ liệu)
        /*
         * 
         */ 

    }
}